package sample.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.hwpf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sample.domain.People;
import sample.domain.Relation;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;

public class FileUtil {

    public static void main(String[] args) throws Exception {

//        People people = toXlsx("C:\\Users\\74611\\Desktop\\文件\\测试5专审.xlsx");
//        People people = toDoc("C:\\Users\\dell\\Desktop\\wenjian\\测试6.doc");
        People people = toXls("C:\\\\Users\\\\dell\\\\Desktop\\\\wenjian2\\\\汪清县支行高山.xls");
        System.out.println(""+people);




    }

    public static People toDoc(String path) throws Exception {
        FileInputStream in = new FileInputStream(path);// 加载文档
        POIFSFileSystem pfs = new POIFSFileSystem(in);
        HWPFDocument hwpf = new HWPFDocument(pfs);
        Range range = hwpf.getRange();// 获取文档的读取范围
        TableIterator iterator = new TableIterator(range);

        People people = new People();
        List relationList = new ArrayList();
        int biyexuexiao = 1;
        while (iterator.hasNext()) {
            Table table = iterator.next();
            // 迭代行，默认从0开始
            for (int i = 0; i < table.numRows(); i++) {
                TableRow tr = table.getRow(i);
                // 迭代列，默认从0开始
                for (int j = 0; j < tr.numCells(); j++) {
                    TableCell td = tr.getCell(j);
                    // 取得单元格的内容
                    String text = "";
                    for (int k = 0; k < td.numParagraphs(); k++) {
                        Paragraph para = td.getParagraph(k);
                        text = text + para.text();
                    }
                    text = text.replaceAll("\\r\\n|\\r|\\n", "");
                    String newText = text.replaceAll("\\s+", "");
                    newText = newText.trim();

                    if (newText.equals("姓名") && j == 0) {
                        people.setName(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("性别")) {
                        people.setSex(docNextText(tr.getCell(j + 1)));
                    } else if (newText.indexOf("出生年月") >= 0) {
                        people.setChushengnianyue(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("民族")) {
                        people.setMinzu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("籍贯")) {
                        people.setJiguan(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("出生地")) {
                        people.setChushengdi(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("入党时间")) {
                        people.setRudangshijian(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("参加工作时间")) {
                        people.setCanjiagongzuo(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("来我行时间")) {
                        people.setLaiwohang(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("健康状况")) {
                        people.setJiankang(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("政治面貌") && j == 0) {
                        String zhengzhimianmaoStr = docNextText(tr.getCell(j + 1));
                        people.setZhengzhimianmao(zhengzhimianmaoStr);
                        if (zhengzhimianmaoStr.indexOf("(") >= 0 && zhengzhimianmaoStr.indexOf(")") >= 0) {
                            people.setRudangshijian(zhengzhimianmaoStr.substring(zhengzhimianmaoStr.indexOf("(") + 1, zhengzhimianmaoStr.indexOf(")")));
                        }

                    } else if (newText.equals("职务级别")) {
                        people.setZhiwujibie(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("现任职务")) {
                        people.setXianrenzhiwu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("拟任职务")) {
                        people.setNirenzhiwu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("拟免职务")) {
                        people.setNimianzhiwu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("专业技术资格")) {
                        people.setJishuzige(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("专业技术职务")) {
                        people.setJishuzhiwu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("熟悉专业有何特长") || newText.equals("熟悉专业有何专长")) {
                        people.setShuxizhuanye(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("全日制教育")) {
                        people.setQuanrizhijiaoyu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("毕业院校系及专业")) {//2次
                        if (biyexuexiao == 1) {
                            people.setRizhixuexiao(docNextText(tr.getCell(j + 1)));
                        } else if (biyexuexiao == 2) {
                            people.setZaizhixuexiao(docNextText(tr.getCell(j + 1)));
                        }
                        biyexuexiao++;
                    } else if (newText.equals("在职教育")) {
                        people.setZaizhijiaoyu(docNextText(tr.getCell(j + 1)));
                    } else if (newText.equals("简历")) {
                        String jianliText = docNextText(tr.getCell(j + 1));
                        jianliText = jianliText(jianliText);
                        jianliText = jianliText.replaceAll("\r","\n");
                        people.setJianli(jianliText);
                    } else if (newText.equals("奖惩情况")) {//下一行是：年度考核结果
                        people.setJiangchengqingkuang(docNextText(tr.getCell(j + 1)).replaceAll("\r","\n"));
                        //考核结果
                        String kaohejieguo = docNextText(table.getRow(i + 1).getCell(j + 1));
                        kaohejieguo = kaohejieguo.replaceAll("，", "");
                        kaohejieguo = kaohejieguo.replaceAll(",", "");
                        people.setNiankaohejieguo(kaohejieguo);
                    } else if (newText.equals("任免理由")) {
                        people.setRenmianliyou(docNextText(tr.getCell(j + 1)));
                    } else if (newText.indexOf("家庭主要成员") >= 0) {
                        boolean whileState = true;
                        int whileNumber = 1;
                        while (whileState) {
                            String zhuyaochengyuan = docNextText(table.getRow(i + whileNumber).getCell(j + 0));
                            zhuyaochengyuan = zhuyaochengyuan.replaceAll("\\r\\n|\\r|\\n", "");
                            zhuyaochengyuan = zhuyaochengyuan.replaceAll("\\s+", "");
                            zhuyaochengyuan = zhuyaochengyuan.trim();
                            if (zhuyaochengyuan.equals("呈报单位")) {
                                whileState = false;
                                continue;
                            }
                            String chengwei = docNextText(table.getRow(i + whileNumber).getCell(j + 1));
                            String name = docNextText(table.getRow(i + whileNumber).getCell(j + 2));
                            String age = docNextText(table.getRow(i + whileNumber).getCell(j + 3));
                            String zhengzhi = docNextText(table.getRow(i + whileNumber).getCell(j + 4));
                            String gongzuo = docNextText(table.getRow(i + whileNumber).getCell(j + 5));
                            gongzuo = qinshuDanweiText(gongzuo);
                            if (!chengwei.equals("")) {
                                Relation relation = new Relation();
                                relation.setChengwei(chengwei);
                                relation.setName(name);
                                relation.setAge(age);
                                relation.setZhengzhimianmao(zhengzhi);
                                relation.setGongzuodanwei(gongzuo);
                                relationList.add(relation);
                            }
                            whileNumber++;
                        }
                        people.setRelationList(relationList);
                    } else if (newText.equals("呈报单位")) {
                        String chengbaodanwei = qudiao(docNextText(tr.getCell(j + 1)), false);
                        people.setChengbaodanwei(chengbaodanwei);
                        //下一行审批意见等
                        String jiguanyijian = docNextText(table.getRow(i + 1).getCell(j + 1));

                        String xingzhengyijian = docNextText(table.getRow(i + 1).getCell(j + 3));

                        jiguanyijian = qudiao(jiguanyijian, false);
                        xingzhengyijian = qudiao(xingzhengyijian, false);
                        people.setJiguanyijian(jiguanyijian);
                        people.setXingzhengyijian(xingzhengyijian);

                    }

                }

            }
        }

        in.close();
        pfs.close();
        hwpf.close();
        return people;
    }

    public static String docNextText(TableCell td) {
        String newtext = "";
        for (int k = 0; k < td.numParagraphs(); k++) {
            Paragraph para = td.getParagraph(k);
            String text = para.text();
//            text = text.replaceAll("\\r\\n|\\r|\\n", "");
//            text = text.replaceAll("\\s+", "");
//            text = text.trim();
            newtext = newtext + text;
//            if(newtext.equals("")){
//                newtext = text;
//            }else{
//                newtext = newtext + "====" + para.text();
//            }
        }
 //       return newtext;
        return newtext.trim();
    }

    public static String qudiao(String str, boolean state) {
        String newStr = str.replaceAll("年", "");
        newStr = newStr.replaceAll("月", "");
        newStr = newStr.replaceAll("日", "");
//        newStr = newStr.replaceAll("\\d", "");
        newStr = newStr.replaceAll("\\(", "");
        newStr = newStr.replaceAll("\\)", "");
        newStr = newStr.replaceAll("（", "");
        newStr = newStr.replaceAll("）", "");
        newStr = newStr.replaceAll("盖章", "");
        return newStr;
    }

    public static String jianliText(String str) {
        String newStr = str.replaceAll("--", "-");
        newStr = newStr.replaceAll("至今", "");
        return newStr;
    }

    public static String xuexiaohzuanye(String str) {
//        String newStr = str.replaceAll("专业", "");
//        return newStr;
        return str;
    }

    public static String qinshuDanweiText(String str) {
//        String newStr = str.replaceAll("无业", "");
//        newStr = newStr.replaceAll("无", "");
//        newStr = newStr.replaceAll("已退休", "");
//        newStr = newStr.replaceAll("已去世", "");
//        newStr = newStr.replaceAll("去世", "");
//        newStr = newStr.replaceAll("\\(", "");
//        newStr = newStr.replaceAll("\\)", "");
//        newStr = newStr.replaceAll("（", "");
//        newStr = newStr.replaceAll("）", "");
//        return newStr;
        return str;
    }

    public static People toXls(String path) throws Exception {
        FileInputStream inputStream = new FileInputStream(new File(path));
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表

        //获取最后一行的num，即总行数。此处从0开始计数
        int maxRow = sheet.getLastRowNum();

        People people = new People();
        List relationList = new ArrayList();
        int biyexuexiao = 1;
        int namecishu = 1;
        int zhengzhimianmaocishu = 1;
        for (int row = 0; row <= maxRow; row++) {
            //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
            int maxRol = sheet.getRow(row).getLastCellNum();
            for (int j = 0; j < maxRol; j++) {
                String text = (sheet.getRow(row).getCell(j) + "").replaceAll("\\r\\n|\\r|\\n", "");
                String newText = text.replaceAll("\\s+", "");
                newText = newText.trim();
                if (newText.equals("姓名") && namecishu == 1) {
                    people.setName(xlsNextText(sheet, row, j, "性别"));
                    namecishu++;
                } else if (newText.equals("性别")) {
                    people.setSex(xlsNextText(sheet, row, j, "出生年月"));
                } else if (newText.indexOf("出生年月") >= 0) {
                    String isChushengdi = xlsNextText((sheet.getRow(row + 1).getCell(j) + ""),true);
                    if (isChushengdi.equals("出生地")) {
                        people.setChushengnianyue(xlsNextText(sheet, row, j, ""));
                    } else {
                        String suiStr = (sheet.getRow(row + 1).getCell(j + 1) + "") + (sheet.getRow(row + 1).getCell(j + 2) + "") + (sheet.getRow(row + 1).getCell(j + 3) + "");
                        suiStr = suiStr.replaceAll("\\.0", "");
                        String nianyueStr = xlsNextText(sheet, row, j, "");
                        people.setChushengnianyue(xlsNextText(nianyueStr + suiStr));
                    }
                } else if (newText.equals("民族")) {
//                    people.setMinzu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setMinzu(xlsNextText(sheet, row, j, "籍贯"));
                } else if (newText.equals("籍贯")) {
//                    people.setJiguan(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiguan(xlsNextText(sheet, row, j, "出生地"));
                } else if (newText.equals("出生地")) {
//                    people.setChushengdi(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setChushengdi(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("入党时间")) {
//                    people.setRudangshijian(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setRudangshijian(xlsNextText(sheet, row, j, "参加工作时间"));
                } else if (newText.equals("参加工作时间")) {
//                    people.setCanjiagongzuo(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setCanjiagongzuo(xlsNextText(sheet, row, j, "来我行时间"));
                } else if (newText.equals("来我行时间")) {
//                    people.setLaiwohang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setLaiwohang(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("健康状况")) {
//                    people.setJiankang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiankang(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("政治面貌") && zhengzhimianmaocishu == 1) {
//                    people.setZhengzhimianmao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    String zhengzhimianmaoStr = xlsNextText(sheet, row, j, "参加工作时间");
                    people.setZhengzhimianmao(zhengzhimianmaoStr);
                    zhengzhimianmaocishu++;
                    if (zhengzhimianmaoStr.indexOf("(") >= 0 && zhengzhimianmaoStr.indexOf(")") >= 0) {
                        people.setRudangshijian(zhengzhimianmaoStr.substring(zhengzhimianmaoStr.indexOf("(") + 1, zhengzhimianmaoStr.indexOf(")")));
                    }

                } else if (newText.equals("职务级别")) {
//                    people.setZhiwujibie(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setZhiwujibie(xlsNextText(sheet, row, j, "专业技术资格"));
                } else if (newText.equals("现任职务")) {
//                    people.setXianrenzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setXianrenzhiwu(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("拟任职务")) {
//                    people.setNirenzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setNirenzhiwu(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("拟免职务")) {
//                    people.setNimianzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setNimianzhiwu(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("专业技术资格")) {
//                    people.setJishuzige(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJishuzige(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("专业技术职务")) {
//                    people.setJishuzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJishuzhiwu(xlsNextText(sheet, row, j, "熟悉专业有何特长"));
                } else if (newText.equals("熟悉专业有何特长") || newText.equals("熟悉专业有何专长")) {
//                    people.setShuxizhuanye(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setShuxizhuanye(xlsNextText(sheet, row, j, ""));
                } else if (newText.equals("全日制教育")) {
                    String isStr = xlsNextText((sheet.getRow(row + 1).getCell(j) + ""));
                    if (isStr.equals("在职教育")) {
                        people.setQuanrizhijiaoyu(xlsNextText(sheet, row, j, "毕业院校系及专业"));
                    } else {
                        String pinStr1 = xlsNextText(sheet, row, j, "毕业院校系及专业");
                        String pinStr2 = (sheet.getRow(row + 1).getCell(j + 1) + "");
                        people.setQuanrizhijiaoyu(xlsNextText(pinStr1 + pinStr2));
                    }
                } else if (newText.equals("毕业院校系及专业")) {//2次
                    if (biyexuexiao == 1) {
                        String isStr = xlsNextText((sheet.getRow(row + 1).getCell(j) + ""));
                        if (isStr.equals("毕业院校系及专业")) {
                            String xuexiaozhuanyeStr = xuexiaohzuanye(xlsNextText(sheet, row, j, ""));
                            people.setRizhixuexiao(xuexiaozhuanyeStr);
                        } else {
                            String pinStr1 = xlsNextText(sheet, row, j, "");
                            String pinStr2 = (sheet.getRow(row + 1).getCell(j + 1) + "");
                            people.setRizhixuexiao(xuexiaohzuanye(xlsNextText(pinStr1 + pinStr2)));
                        }
                    } else if (biyexuexiao == 2) {
                        String isStr = xlsNextText((sheet.getRow(row - 1).getCell(j) + ""));
                        if (isStr.equals("毕业院校系及专业")) {
                            String xuexiaozhuanyeStr = xuexiaohzuanye(xlsNextText(sheet, row, j, ""));
                            people.setZaizhixuexiao(xuexiaozhuanyeStr);
                        } else {
                            String pinStr1 = xlsNextText(sheet, row, j, "");
                            String pinStr2 = (sheet.getRow(row + 1).getCell(j + 1) + "");
                            people.setZaizhixuexiao(xuexiaohzuanye(xlsNextText(pinStr1 + pinStr2)));
                        }
                    }
                    biyexuexiao++;
                } else if (newText.equals("在职教育")) {
                    String isStr = xlsNextText((sheet.getRow(row - 1).getCell(j) + ""));
                    if (isStr.equals("全日制教育")) {
                        people.setZaizhijiaoyu(xlsNextText(sheet, row, j, "毕业院校系及专业"));
                    } else {
                        String pinStr1 = xlsNextText(sheet, row, j, "");
                        String pinStr2 = (sheet.getRow(row + 1).getCell(j + 1) + "");
                        people.setZaizhijiaoyu(xlsNextText(pinStr1 + pinStr2));
                    }
                } else if (newText.equals("简历")) {
                    int jianli_index = 0;
                    String jianliTest = xlsNextText((sheet.getRow(row).getCell(j + 1) + ""));
                    if (jianliTest == null || jianliTest.equals("")) {
                        jianli_index = 1;
                    }
                    String jianliText = xlsNextText(sheet, row + jianli_index, j, "");
                    jianliText = jianliText(jianliText);
                    people.setJianli(jianliText);
                } else if (newText.equals("奖惩情况")) {//下一行是：年度考核结果
                    //people.setJiangchengqingkuang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiangchengqingkuang(xlsNextText(sheet, row, j, ""));
                    //考核结果
                    //String kaohejieguo = xlsNextText((sheet.getRow(row+1).getCell(j+1) + ""));
                    String kaohejieguo = xlsNextText(sheet, row + 1, j, "");
//                    kaohejieguo = kaohejieguo.replaceAll("，", "");
//                    kaohejieguo = kaohejieguo.replaceAll(",", "");
                    people.setNiankaohejieguo(kaohejieguo);
                } else if (newText.equals("任免理由")) {
                    //people.setRenmianliyou(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setRenmianliyou(xlsNextText(sheet, row, j, ""));
                } else if (newText.indexOf("家庭主要成员") >= 0) {
                    boolean whileState = true;
                    int whileNumber = 1;
                    while (whileState) {
                        String zhuyaochengyuan = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 0) + ""),true);
                        if (zhuyaochengyuan.equals("呈报单位")) {
                            whileState = false;
                            continue;
                        }
                        String chengwei = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 1) + ""));
                        String name = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 3) + ""));
                        String age = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 5) + ""));
                        if (age != null) {
                            age = age.replaceAll("\\.0", "");
                        }
                        String zhengzhi = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 6) + ""));
                        //String gongzuo = xlsNextText((sheet.getRow(row+whileNumber).getCell(j+6) + ""));
                        String gongzuo = xlsNextText(sheet, row + whileNumber, j + 6, "");
                        gongzuo = qinshuDanweiText(gongzuo);
                        if (!chengwei.equals("")) {
                            Relation relation = new Relation();
                            relation.setChengwei(chengwei);
                            relation.setName(name);
                            relation.setAge(age);
                            relation.setZhengzhimianmao(zhengzhi);
                            relation.setGongzuodanwei(gongzuo);
                            relationList.add(relation);
                        }
                        whileNumber++;
                    }
                    people.setRelationList(relationList);
                } else if (newText.equals("呈报单位")) {
                    //String chengbaodanwei = qudiao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")),false);
                    String chengbaodanwei = qudiao(xlsNextText(sheet, row, j, ""), false);
                    people.setChengbaodanwei(chengbaodanwei);
                    //下一行审批意见等
                    //String jiguanyijian = xlsNextText((sheet.getRow(row+1).getCell(j+1) + ""));;
                    //String xingzhengyijian = xlsNextText((sheet.getRow(row+1).getCell(j+3) + ""));
                    String jiguanyijian = xlsNextText(sheet, row + 1, j, "行任");
//                    String xingzhengyijian = xlsxNextText(sheet,row+1,j,"");
                    jiguanyijian = qudiao(jiguanyijian, false);
//                    xingzhengyijian = qudiao(xingzhengyijian,false);
                    people.setJiguanyijian(jiguanyijian);
//                    people.setXingzhengyijian(xingzhengyijian);

                }

            }
        }

        if(workbook.getNumberOfSheets()>1){
            sheet = workbook.getSheetAt(1); // 读取第一个工作表
            //获取最后一行的num，即总行数。此处从0开始计数
            maxRow = sheet.getLastRowNum();

            relationList = new ArrayList();
            for (int row = 0; row <= maxRow; row++) {
                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                int maxRol = sheet.getRow(row).getLastCellNum();
                for (int j = 0; j < maxRol; j++) {
                    String text = (sheet.getRow(row).getCell(j) + "").replaceAll("\\r\\n|\\r|\\n", "");
                    String newText = text.replaceAll("\\s+", "");
                    newText = newText.trim();
                   if (newText.equals("奖惩情况")) {//下一行是：年度考核结果
                        people.setJiangchengqingkuang(xlsNextText(sheet, row, j, ""));
                        //考核结果
                        String kaohejieguo = xlsNextText(sheet, row + 2, j, "");
                        people.setNiankaohejieguo(kaohejieguo);
                    } else if (newText.equals("任免理由")) {
                        //people.setRenmianliyou(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                        people.setRenmianliyou(xlsNextText(sheet, row, j, ""));
                    } else if (newText.indexOf("家庭主要成员") >= 0) {
                        boolean whileState = true;
                        int whileNumber = 1;
                        while (whileState) {
                            String zhuyaochengyuan = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 0) + ""),true);
                            if (zhuyaochengyuan.equals("呈报单位")) {
                                whileState = false;
                                continue;
                            }
                            String chengwei = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 1) + ""));
                            String name = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 2) + ""));
                            String age = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 3) + ""));
                            if (age != null) {
                                age = age.replaceAll("\\.0", "");
                            }
                            String zhengzhi = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 4) + ""));
                            //String gongzuo = xlsNextText((sheet.getRow(row+whileNumber).getCell(j+6) + ""));
                            String gongzuo = xlsNextText(sheet, row + whileNumber, j + 4, "");
                            gongzuo = qinshuDanweiText(gongzuo);
                            if (!chengwei.equals("")) {
                                Relation relation = new Relation();
                                relation.setChengwei(chengwei);
                                relation.setName(name);
                                relation.setAge(age);
                                relation.setZhengzhimianmao(zhengzhi);
                                relation.setGongzuodanwei(gongzuo);
                                relationList.add(relation);
                            }
                            whileNumber++;
                        }
                        people.setRelationList(relationList);
                    } else if (newText.equals("呈报单位")) {
                        String chengbaodanwei = qudiao(xlsNextText(sheet, row, j, ""), false);
                        people.setChengbaodanwei(chengbaodanwei);
                        //下一行审批意见等
                        String jiguanyijian = xlsNextText(sheet, row + 6, j, "行任");
                        jiguanyijian = qudiao(jiguanyijian, false);
                        people.setJiguanyijian(jiguanyijian);

                       String xingzhengyijian = xlsNextText(sheet, row + 6, j + 5, "");
                       xingzhengyijian = qudiao(xingzhengyijian, false);
                       people.setXingzhengyijian(xingzhengyijian);

                    }
                }
            }
        }

        inputStream.close();
        workbook.close();
        return people;
    }

    public static String xlsNextText(Sheet sheet, int row, int j, String name) {
        String newText = "";
        String returnStr = "";
        for (int i = 1; i <= 15; i++) {
            String str = (sheet.getRow(row).getCell(j + i)) + "";
            if (str == null || str.equals("") || str.equals("null")) {
                continue;
            }
            newText = str.replaceAll("\\r\\n|\\r|\\n", "");
            newText = newText.replaceAll("\\s+", "");
            newText = newText.trim();

            returnStr = str;
            if (!name.equals("")) {
                if (newText.equals(name) || newText.indexOf(name) >= 0) {
                    return "";
                }
            }
            break;
        }
        return returnStr;
    }


    public static String xlsNextText(String str) {
//        String newText = str.replaceAll("\\r\\n|\\r|\\n", "");
//        newText = newText.replaceAll("\\s+", "");
//        newText = newText.trim();
//        return newText;
        return str;
    }
    public static String xlsNextText(String str,boolean isQudiao) {
        String newText = str.replaceAll("\\r\\n|\\r|\\n", "");
        newText = newText.replaceAll("\\s+", "");
        newText = newText.trim();
        return newText;
    }


    public static People toXlsx(String path) throws Exception {
        //创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(path));
//        System.out.println("xssfWorkbook对象：" + xssfWorkbook);
        //读取第一个工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
//        System.out.println("sheet对象：" + sheet);
        //获取最后一行的num，即总行数。此处从0开始计数
        int maxRow = sheet.getLastRowNum();

        People people = new People();
        List relationList = new ArrayList();
        int biyexuexiao = 1;
        int namecishu = 1;
        int zhengzhimianmaocishu = 1;
        for (int row = 0; row <= maxRow; row++) {
            //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
            int maxRol = sheet.getRow(row).getLastCellNum();
//            System.out.println("--------第" + row + "行的数据如下--------");
            for (int j = 0; j < maxRol; j++) {
                String text = (sheet.getRow(row).getCell(j) + "").replaceAll("\\r\\n|\\r|\\n", "");
                String newText = text.replaceAll("\\s+", "");
                newText = newText.trim();
//                System.out.println(newText+"=="+j);
                if (newText.equals("姓名") && namecishu == 1) {
//                    people.setName(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setName(xlsxNextText(sheet, row, j, "性别"));
                    namecishu++;
                } else if (newText.equals("性别")) {
//                    people.setSex(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setSex(xlsxNextText(sheet, row, j, "出生年月"));
                } else if (newText.indexOf("出生年月") >= 0) {
//                    people.setChushengnianyue(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setChushengnianyue(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("民族")) {
//                    people.setMinzu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setMinzu(xlsxNextText(sheet, row, j, "籍贯"));
                } else if (newText.equals("籍贯")) {
//                    people.setJiguan(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiguan(xlsxNextText(sheet, row, j, "出生地"));
                } else if (newText.equals("出生地")) {
//                    people.setChushengdi(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setChushengdi(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("入党时间")) {
//                    people.setRudangshijian(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setRudangshijian(xlsxNextText(sheet, row, j, "参加工作时间"));
                } else if (newText.equals("参加工作时间")) {
//                    people.setCanjiagongzuo(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setCanjiagongzuo(xlsxNextText(sheet, row, j, "健康状况"));
                } else if (newText.equals("来我行时间")) {
//                    people.setLaiwohang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setLaiwohang(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("健康状况")) {
//                    people.setJiankang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiankang(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("政治面貌") && zhengzhimianmaocishu == 1) {
//                    people.setZhengzhimianmao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    String zhengzhimianmaoStr = xlsxNextText(sheet, row, j, "参加工作时间");
                    people.setZhengzhimianmao(zhengzhimianmaoStr);
                    zhengzhimianmaocishu++;
                    if (zhengzhimianmaoStr.indexOf("(") >= 0 && zhengzhimianmaoStr.indexOf(")") >= 0) {
                        people.setRudangshijian(zhengzhimianmaoStr.substring(zhengzhimianmaoStr.indexOf("(") + 1, zhengzhimianmaoStr.indexOf(")")));
                    }
                } else if (newText.equals("职务级别")) {
//                    people.setZhiwujibie(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setZhiwujibie(xlsxNextText(sheet, row, j, "专业技术资格"));
                } else if (newText.equals("现任职务")) {
//                    people.setXianrenzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setXianrenzhiwu(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("拟任职务")) {
//                    people.setNirenzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setNirenzhiwu(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("拟免职务")) {
//                    people.setNimianzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setNimianzhiwu(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("专业技术资格")) {
//                    people.setJishuzige(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJishuzige(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("专业技术职务")) {
//                    people.setJishuzhiwu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJishuzhiwu(xlsxNextText(sheet, row, j, "熟悉专业有何特长"));
                } else if (newText.equals("熟悉专业有何特长") || newText.equals("熟悉专业有何专长")) {
//                    people.setShuxizhuanye(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setShuxizhuanye(xlsxNextText(sheet, row, j, ""));
                } else if (newText.equals("全日制教育")) {
//                    people.setQuanrizhijiaoyu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setQuanrizhijiaoyu(xlsxNextText(sheet, row, j, "毕业院校系及专业"));
                } else if (newText.equals("毕业院校系及专业")) {//2次
                    if (biyexuexiao == 1) {
//                        people.setRizhixuexiao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                        people.setRizhixuexiao(xlsxNextText(sheet, row, j, ""));
                    } else if (biyexuexiao == 2) {
//                        people.setZaizhixuexiao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                        people.setZaizhixuexiao(xlsxNextText(sheet, row, j, ""));
                    }
                    biyexuexiao++;
                } else if (newText.equals("在职教育")) {
//                    people.setZaizhijiaoyu(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setZaizhijiaoyu(xlsxNextText(sheet, row, j, "毕业院校系及专业"));
                } else if (newText.equals("简历")) {
                    int jianli_index = 0;
                    String jianliTest = xlsNextText((sheet.getRow(row).getCell(j + 1) + ""));
                    if (jianliTest == null || jianliTest.equals("")) {
                        jianli_index = 1;
                    }
                    String jianliText = xlsxNextText(sheet, row + jianli_index, j, "");
                    jianliText = jianliText(jianliText);
                    people.setJianli(jianliText);
                } else if (newText.equals("奖惩情况")) {//下一行是：年度考核结果
                    //people.setJiangchengqingkuang(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setJiangchengqingkuang(xlsxNextText(sheet, row, j, ""));
                    //考核结果
                    //String kaohejieguo = xlsNextText((sheet.getRow(row+1).getCell(j+1) + ""));
                    String kaohejieguo = xlsxNextText(sheet, row + 1, j, "");
//                    kaohejieguo = kaohejieguo.replaceAll("，", "");
//                    kaohejieguo = kaohejieguo.replaceAll(",", "");
                    people.setNiankaohejieguo(kaohejieguo);
                } else if (newText.equals("任免理由")) {
                    //people.setRenmianliyou(xlsNextText((sheet.getRow(row).getCell(j+1) + "")));
                    people.setRenmianliyou(xlsxNextText(sheet, row, j, ""));
                } else if (newText.indexOf("家庭主要成员") >= 0) {
                    boolean whileState = true;
                    int whileNumber = 1;
                    while (whileState) {
                        String zhuyaochengyuan = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 0) + ""),true);
                        if (zhuyaochengyuan.equals("呈报单位")) {
                            whileState = false;
                            continue;
                        }
                        String chengwei = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 1) + ""));
                        String name = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 2) + ""));
                        String age = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 4) + ""));
                        if (age != null) {
                            age = age.replaceAll("\\.0", "");
                        }
                        String zhengzhi = xlsNextText((sheet.getRow(row + whileNumber).getCell(j + 5) + ""));
                        //String gongzuo = xlsNextText((sheet.getRow(row+whileNumber).getCell(j+6) + ""));
                        String gongzuo = xlsxNextText(sheet, row + whileNumber, j + 5, "");
                        gongzuo = qinshuDanweiText(gongzuo);
                        if (!chengwei.equals("")) {
                            Relation relation = new Relation();
                            relation.setChengwei(chengwei);
                            relation.setName(name);
                            relation.setAge(age);
                            relation.setZhengzhimianmao(zhengzhi);
                            relation.setGongzuodanwei(gongzuo);
                            relationList.add(relation);
                        }
                        whileNumber++;
                    }
                    people.setRelationList(relationList);
                } else if (newText.equals("呈报单位")) {
                    //String chengbaodanwei = qudiao(xlsNextText((sheet.getRow(row).getCell(j+1) + "")),false);
                    String chengbaodanwei = qudiao(xlsxNextText(sheet, row, j, ""), false);
                    people.setChengbaodanwei(chengbaodanwei);
                    //下一行审批意见等
                    //String jiguanyijian = xlsNextText((sheet.getRow(row+1).getCell(j+1) + ""));;
                    //String xingzhengyijian = xlsNextText((sheet.getRow(row+1).getCell(j+3) + ""));
                    String jiguanyijian = xlsxNextText(sheet, row + 1, j, "行任");
                    jiguanyijian = qudiao(jiguanyijian, false);
                    people.setJiguanyijian(jiguanyijian);
                    String xingzhengyijian = xlsxNextText(sheet, row + 1, j + 6, "");
                    xingzhengyijian = qudiao(xingzhengyijian, false);
                    people.setXingzhengyijian(xingzhengyijian);
                }

            }
        }
        xssfWorkbook.close();
        return people;
    }

    public static String xlsxNextText(XSSFSheet sheet, int row, int j, String name) {
        String newText = "";
        String returnStr = "";
        for (int i = 1; i <= 15; i++) {
//            System.out.println("循环次数=="+i);
            String str = (sheet.getRow(row).getCell(j + i)) + "";
            if (str == null || str.equals("") || str.equals("null")) {
                continue;
            }
            newText = str.replaceAll("\\r\\n|\\r|\\n", "");
            newText = newText.replaceAll("\\s+", "");
            newText = newText.trim();

            returnStr = str;
            if (!name.equals("")) {
                if (newText.equals(name) || newText.indexOf(name) >= 0) {
                    return "";
                }
            }
            break;
        }
        return returnStr;
    }

    public static People bijiaoPeople(People leftPeople, People rightPeople) {
        List<String> returnList = new ArrayList();
        People returnPeople = new People();
        if (leftPeople.getName() != null && rightPeople.getName() != null) {
            String leftStr = leftPeople.getName().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getName().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("姓名");
                returnPeople.setName("false");
            }
        } else {
            returnPeople.setName("不存在");
        }

        if (leftPeople.getSex() != null && rightPeople.getSex() != null) {
            String leftStr = leftPeople.getSex().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getSex().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("性别");
                returnPeople.setSex("false");
            }
        } else {
            returnPeople.setSex("不存在");
        }

        if (leftPeople.getChushengnianyue() != null && rightPeople.getChushengnianyue() != null) {
            String leftStr = leftPeople.getChushengnianyue().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getChushengnianyue().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("出生年月");
                returnPeople.setChushengnianyue("false");
            }
        } else {
            returnPeople.setChushengnianyue("不存在");
        }

        if (leftPeople.getMinzu() != null && rightPeople.getMinzu() != null) {
            String leftStr = leftPeople.getMinzu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getMinzu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("民族");
                returnPeople.setMinzu("false");
            }
        } else {
            returnPeople.setMinzu("不存在");
        }

        if (leftPeople.getJiguan() != null && rightPeople.getJiguan() != null) {
            String leftStr = leftPeople.getJiguan().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJiguan().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("籍贯");
                returnPeople.setJiguan("false");
            }
        } else {
            returnPeople.setJiguan("不存在");
        }

        if (leftPeople.getChushengdi() != null && rightPeople.getChushengdi() != null) {
            String leftStr = leftPeople.getChushengdi().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getChushengdi().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("出生地");
                returnPeople.setChushengdi("false");
            }
        } else {
            returnPeople.setChushengdi("不存在");
        }

        if (leftPeople.getZhengzhimianmao() != null && rightPeople.getZhengzhimianmao() != null) {
            String leftStr = leftPeople.getZhengzhimianmao().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getZhengzhimianmao().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("政治面貌");
                returnPeople.setZhengzhimianmao("false");
            }
        } else {
            returnPeople.setZhengzhimianmao("不存在");
        }

        if (leftPeople.getCanjiagongzuo() != null && rightPeople.getCanjiagongzuo() != null) {
            String leftStr = leftPeople.getCanjiagongzuo().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getCanjiagongzuo().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("参加工作时间");
                returnPeople.setCanjiagongzuo("false");
            }
        } else {
            returnPeople.setCanjiagongzuo("不存在");
        }

        if (leftPeople.getLaiwohang() != null && rightPeople.getLaiwohang() != null) {
            String leftStr = leftPeople.getLaiwohang().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getLaiwohang().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("来我行时间");
                returnPeople.setLaiwohang("false");
            }
        } else {
            returnPeople.setLaiwohang("不存在");
        }

        if (leftPeople.getZhiwujibie() != null && rightPeople.getZhiwujibie() != null) {
            String leftStr = leftPeople.getZhiwujibie().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getZhiwujibie().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("职务级别");
                returnPeople.setZhiwujibie("false");
            }
        } else {
            returnPeople.setZhiwujibie("不存在");
        }

        if (leftPeople.getJishuzige() != null && rightPeople.getJishuzige() != null) {
            String leftStr = leftPeople.getJishuzige().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJishuzige().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("专业技术资格");
                returnPeople.setJishuzige("false");
            }
        } else {
            returnPeople.setJishuzige("不存在");
        }

        if (leftPeople.getShuxizhuanye() != null && rightPeople.getShuxizhuanye() != null) {
            String leftStr = leftPeople.getShuxizhuanye().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getShuxizhuanye().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("熟悉专业有何特长");
                returnPeople.setShuxizhuanye("false");
            }
        } else {
            returnPeople.setShuxizhuanye("不存在");
        }

        if (leftPeople.getRudangshijian() != null && rightPeople.getRudangshijian() != null) {
            String leftStr = leftPeople.getRudangshijian().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getRudangshijian().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("入党时间");
                returnPeople.setRudangshijian("false");
            }
        } else {
            returnPeople.setRudangshijian("不存在");
        }

        if (leftPeople.getJiankang() != null && rightPeople.getJiankang() != null) {
            String leftStr = leftPeople.getJiankang().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJiankang().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("健康状况");
                returnPeople.setJiankang("false");
            }
        } else {
            returnPeople.setJiankang("不存在");
        }

        if (leftPeople.getJishuzhiwu() != null && rightPeople.getJishuzhiwu() != null) {
            String leftStr = leftPeople.getJishuzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJishuzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("专业技术职务");
                returnPeople.setJishuzhiwu("false");
            }
        } else {
            returnPeople.setJishuzhiwu("不存在");
        }

        if (leftPeople.getQuanrizhijiaoyu() != null && rightPeople.getQuanrizhijiaoyu() != null) {
            String leftStr = leftPeople.getQuanrizhijiaoyu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getQuanrizhijiaoyu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("全日制教育");
                returnPeople.setQuanrizhijiaoyu("false");
            }
        } else {
            returnPeople.setQuanrizhijiaoyu("不存在");
        }

        if (leftPeople.getRizhixuexiao() != null && rightPeople.getRizhixuexiao() != null) {
            String leftStr = leftPeople.getRizhixuexiao().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getRizhixuexiao().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("毕业院校及专业");
                returnPeople.setRizhixuexiao("false");
            }
        } else {
            returnPeople.setRizhixuexiao("不存在");
        }

        if (leftPeople.getZaizhijiaoyu() != null && rightPeople.getZaizhijiaoyu() != null) {
            String leftStr = leftPeople.getZaizhijiaoyu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getZaizhijiaoyu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("在职教育");
                returnPeople.setZaizhijiaoyu("false");
            }
        } else {
            returnPeople.setZaizhijiaoyu("不存在");
        }

        if (leftPeople.getZaizhixuexiao() != null && rightPeople.getZaizhixuexiao() != null) {
            String leftStr = leftPeople.getZaizhixuexiao().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getZaizhixuexiao().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("毕业院校及专业");
                returnPeople.setZaizhixuexiao("false");
            }
        } else {
            returnPeople.setZaizhixuexiao("不存在");
        }

        if (leftPeople.getXianrenzhiwu() != null && rightPeople.getXianrenzhiwu() != null) {
            String leftStr = leftPeople.getXianrenzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getXianrenzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("现任职务");
                returnPeople.setXianrenzhiwu("false");
            }
        } else {
            returnPeople.setXianrenzhiwu("不存在");
        }

        if (leftPeople.getNirenzhiwu() != null && rightPeople.getNirenzhiwu() != null) {
            String leftStr = leftPeople.getNirenzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getNirenzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("拟任职务");
                returnPeople.setNirenzhiwu("false");
            }
        } else {
            returnPeople.setNirenzhiwu("不存在");
        }

        if (leftPeople.getNimianzhiwu() != null && rightPeople.getNimianzhiwu() != null) {
            String leftStr = leftPeople.getNimianzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getNimianzhiwu().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("拟免职务");
                returnPeople.setNimianzhiwu("false");
            }
        } else {
            returnPeople.setNimianzhiwu("不存在");
        }

        if (leftPeople.getJianli() != null && rightPeople.getJianli() != null) {
            String leftStr = leftPeople.getJianli().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJianli().replaceAll("\\r\\n|\\r|\\n", "");

            leftStr = leftStr.replaceAll("\\s+", "");
            leftStr = leftStr.trim();
            rightStr = rightStr.replaceAll("\\s+", "");
            rightStr = rightStr.trim();

            if (!leftStr.equals(rightStr)) {
                returnList.add("简历");
                returnPeople.setJianli("false");
            }
        } else {
            returnPeople.setJianli("不存在");
        }

        if (leftPeople.getJiangchengqingkuang() != null && rightPeople.getJiangchengqingkuang() != null) {
            String leftStr = leftPeople.getJiangchengqingkuang().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJiangchengqingkuang().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("奖惩情况");
                returnPeople.setJiangchengqingkuang("false");
            }
        } else {
            returnPeople.setJiangchengqingkuang("不存在");
        }

        if (leftPeople.getNiankaohejieguo() != null && rightPeople.getNiankaohejieguo() != null) {
            String leftStr = leftPeople.getNiankaohejieguo().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getNiankaohejieguo().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("年度考核结果");
                returnPeople.setNiankaohejieguo("false");
            }
        } else {
            returnPeople.setNiankaohejieguo("不存在");
        }

        if (leftPeople.getRenmianliyou() != null && rightPeople.getRenmianliyou() != null) {
            String leftStr = leftPeople.getRenmianliyou().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getRenmianliyou().replaceAll("\\r\\n|\\r|\\n", "");
            if (!leftStr.equals(rightStr)) {
                returnList.add("任免理由");
                returnPeople.setRenmianliyou("false");
            }
        } else {
            returnPeople.setRenmianliyou("不存在");
        }

        if (leftPeople.getChengbaodanwei() != null && rightPeople.getChengbaodanwei() != null) {
            String leftStr = leftPeople.getChengbaodanwei().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getChengbaodanwei().replaceAll("\\r\\n|\\r|\\n", "");
            leftStr = leftStr.replaceAll("\\s+", "");
            leftStr = leftStr.trim();
            rightStr = rightStr.replaceAll("\\s+", "");
            rightStr = rightStr.trim();
            if (!leftStr.equals(rightStr)) {
                returnList.add("呈报单位");
                returnPeople.setChengbaodanwei("false");
            }
        } else {
            returnPeople.setChengbaodanwei("不存在");
        }

        if (leftPeople.getJiguanyijian() != null && rightPeople.getJiguanyijian() != null) {
            String leftStr = leftPeople.getJiguanyijian().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getJiguanyijian().replaceAll("\\r\\n|\\r|\\n", "");
            leftStr = leftStr.replaceAll("\\s+", "");
            leftStr = leftStr.trim();
            rightStr = rightStr.replaceAll("\\s+", "");
            rightStr = rightStr.trim();
            if (!leftStr.equals(rightStr)) {
                returnList.add("审批机关意见");
                returnPeople.setJiguanyijian("false");
            }
        } else {
            returnPeople.setJiguanyijian("不存在");
        }

        if (leftPeople.getXingzhengyijian() != null && rightPeople.getXingzhengyijian() != null) {
            String leftStr = leftPeople.getXingzhengyijian().replaceAll("\\r\\n|\\r|\\n", "");
            String rightStr = rightPeople.getXingzhengyijian().replaceAll("\\r\\n|\\r|\\n", "");
            leftStr = leftStr.replaceAll("\\s+", "");
            leftStr = leftStr.trim();
            rightStr = rightStr.replaceAll("\\s+", "");
            rightStr = rightStr.trim();
            if (!leftStr.equals(rightStr)) {
                returnList.add("行政机关任免意见");
                returnPeople.setXingzhengyijian("false");
            }
        } else {
            returnPeople.setXingzhengyijian("不存在");
        }

        if (leftPeople.getRelationList() != null && rightPeople.getRelationList() != null) {
            if (leftPeople.getRelationList().size() != rightPeople.getRelationList().size()) {
                returnList.add("家庭主要成员");
                returnPeople.setRelationStr("false");
            } else {
                w:
                for (Relation leftRelation : leftPeople.getRelationList()) {
                    boolean relationState = true;
                    for (Relation rightRelation : rightPeople.getRelationList()) {
                        if (leftRelation.getChengwei().equals(rightRelation.getChengwei())) {
                            if (!leftRelation.getName().equals(rightRelation.getName())) {
                                returnList.add("家庭主要成员");
                                returnPeople.setRelationStr("false");
                                break w;
                            }
                            if (!leftRelation.getAge().equals(rightRelation.getAge())) {
                                returnList.add("家庭主要成员");
                                returnPeople.setRelationStr("false");
                                break w;
                            }
                            if (!leftRelation.getZhengzhimianmao().equals(rightRelation.getZhengzhimianmao())) {
                                returnList.add("家庭主要成员");
                                returnPeople.setRelationStr("false");
                                break w;
                            }
                            if (!leftRelation.getGongzuodanwei().equals(rightRelation.getGongzuodanwei())) {
                                returnList.add("家庭主要成员");
                                returnPeople.setRelationStr("false");
                                break w;
                            }
                            relationState = false;
                        }
                    }
                    if (relationState) {
                        returnList.add("家庭主要成员");
                        returnPeople.setRelationStr("false");
                        break w;
                    }
                }
            }
        } else {
            returnPeople.setRelationStr("不存在");
        }

        returnPeople.setRemarkList(returnList);
        return returnPeople;
    }

    public static void createExcel(List<People> peopleList) {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("ExampleSheet");

        // 创建行和单元格
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(18);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("文件名称");
        titleCell = titleRow.createCell(1);
        titleCell.setCellValue("姓名");
        titleCell = titleRow.createCell(2);
        titleCell.setCellValue("性别");
        titleCell = titleRow.createCell(3);
        titleCell.setCellValue("出生年月");
        titleCell = titleRow.createCell(4);
        titleCell.setCellValue("民族");
        titleCell = titleRow.createCell(5);
        titleCell.setCellValue("籍贯");
        titleCell = titleRow.createCell(6);
        titleCell.setCellValue("出生地");
        titleCell = titleRow.createCell(7);
        titleCell.setCellValue("政治面貌");
        titleCell = titleRow.createCell(8);
        titleCell.setCellValue("参加工作时间");
        titleCell = titleRow.createCell(9);
        titleCell.setCellValue("来我行时间");
        titleCell = titleRow.createCell(10);
        titleCell.setCellValue("职务级别");
        titleCell = titleRow.createCell(11);
        titleCell.setCellValue("专业技术资格");
        titleCell = titleRow.createCell(12);
        titleCell.setCellValue("熟悉专业有何特长");
        titleCell = titleRow.createCell(13);
        titleCell.setCellValue("入党时间");
        titleCell = titleRow.createCell(14);
        titleCell.setCellValue("健康状况");
        titleCell = titleRow.createCell(15);
        titleCell.setCellValue("专业技术职务");
        titleCell = titleRow.createCell(16);
        titleCell.setCellValue("全日制教育");
        titleCell = titleRow.createCell(17);
        titleCell.setCellValue("毕业院校及专业");
        titleCell = titleRow.createCell(18);
        titleCell.setCellValue("在职教育");
        titleCell = titleRow.createCell(19);
        titleCell.setCellValue("毕业院校及专业");
        titleCell = titleRow.createCell(20);
        titleCell.setCellValue("现任职务");
        titleCell = titleRow.createCell(21);
        titleCell.setCellValue("拟任职务");
        titleCell = titleRow.createCell(22);
        titleCell.setCellValue("拟免职务");
        titleCell = titleRow.createCell(23);
        titleCell.setCellValue("简历");
        titleCell = titleRow.createCell(24);
        titleCell.setCellValue("奖惩情况");
        titleCell = titleRow.createCell(25);
        titleCell.setCellValue("年度考核结果");
        titleCell = titleRow.createCell(26);
        titleCell.setCellValue("任免理由");
        titleCell = titleRow.createCell(27);
        titleCell.setCellValue("呈报单位");
        titleCell = titleRow.createCell(28);
        titleCell.setCellValue("审批机关意见");
        titleCell = titleRow.createCell(29);
        titleCell.setCellValue("行政机关任免意见");
        titleCell = titleRow.createCell(30);
        titleCell.setCellValue("家庭主要成员");
//        titleCell.setCellStyle(createStyles(workbook).get("title"));
        Map<String, CellStyle> styles = createStyles(workbook);
        for (int i = 0; i < titleRow.getLastCellNum(); i++) {
            titleRow.getCell(i).setCellStyle(styles.get("title"));
            sheet.setColumnWidth(i, 18 * 256);
        }

        int i = 1;
        for (People people : peopleList) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(people.getFileName());
            cell = row.createCell(1);
            cell.setCellValue(people.getName());
            cell = row.createCell(2);
            cell.setCellValue(people.getSex());
            cell = row.createCell(3);
            cell.setCellValue(people.getChushengnianyue());
            cell = row.createCell(4);
            cell.setCellValue(people.getMinzu());
            cell = row.createCell(5);
            cell.setCellValue(people.getJiguan());
            cell = row.createCell(6);
            cell.setCellValue(people.getChushengdi());
            cell = row.createCell(7);
            cell.setCellValue(people.getZhengzhimianmao());
            cell = row.createCell(8);
            cell.setCellValue(people.getCanjiagongzuo());
            cell = row.createCell(9);
            cell.setCellValue(people.getLaiwohang());
            cell = row.createCell(10);
            cell.setCellValue(people.getZhiwujibie());
            cell = row.createCell(11);
            cell.setCellValue(people.getJishuzige());
            cell = row.createCell(12);
            cell.setCellValue(people.getShuxizhuanye());
            cell = row.createCell(13);
            cell.setCellValue(people.getRudangshijian());
            cell = row.createCell(14);
            cell.setCellValue(people.getJiankang());
            cell = row.createCell(15);
            cell.setCellValue(people.getJishuzhiwu());
            cell = row.createCell(16);
            cell.setCellValue(people.getQuanrizhijiaoyu());
            cell = row.createCell(17);
            cell.setCellValue(people.getRizhixuexiao());
            cell = row.createCell(18);
            cell.setCellValue(people.getZaizhijiaoyu());
            cell = row.createCell(19);
            cell.setCellValue(people.getZaizhixuexiao());
            cell = row.createCell(20);
            cell.setCellValue(people.getXianrenzhiwu());
            cell = row.createCell(21);
            cell.setCellValue(people.getNirenzhiwu());
            cell = row.createCell(22);
            cell.setCellValue(people.getNimianzhiwu());
            cell = row.createCell(23);
            cell.setCellValue(people.getJianli());
            cell = row.createCell(24);
            cell.setCellValue(people.getJiangchengqingkuang());
            cell = row.createCell(25);
            cell.setCellValue(people.getNiankaohejieguo());
            cell = row.createCell(26);
            cell.setCellValue(people.getRenmianliyou());
            cell = row.createCell(27);
            cell.setCellValue(people.getChengbaodanwei());
            cell = row.createCell(28);
            cell.setCellValue(people.getJiguanyijian());
            cell = row.createCell(29);
            cell.setCellValue(people.getXingzhengyijian());
            cell = row.createCell(30);
            cell.setCellValue(people.getRelationStr());
            i++;
        }

        // 获取当前exe文件的路径
        String currentPath = Paths.get("").toAbsolutePath().toString();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = dateFormat.format(new Date());
        fileName = currentPath + "/" + fileName + "比对结果.xlsx";

        try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
            // 将工作簿写入文件
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void createExcel(People leftPeople, People rightPeople, People resultPeople) {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("ExampleSheet");


        // 创建一个单元格样式，设置自动换行
        CellStyle huanhangStyle = workbook.createCellStyle();
        huanhangStyle.setWrapText(true);

        CellStyle yanseStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        yanseStyle.setWrapText(true);
        yanseStyle.setFont(font);

        XSSFFont xssfFont = new XSSFWorkbook().createFont();
        xssfFont.setColor(IndexedColors.RED.getIndex());

        int relationLength = 0;
        if (leftPeople.getRelationList() != null) {
            relationLength = leftPeople.getRelationList().size();
        }
        if (rightPeople.getRelationList() != null) {
            if (relationLength < rightPeople.getRelationList().size()) {
                relationLength = rightPeople.getRelationList().size();
            }
        }

        for (int i = 0; i <= 31 + relationLength; i++) {
            sheet.createRow(i);
            sheet.getRow(i).setHeightInPoints(26);
            //sheet.setColumnWidth(i, 18 * 256);
            for (int j = 0; j < 4; j++) {
                sheet.getRow(i).createCell(j);
                sheet.getRow(i).getCell(j).setCellStyle(huanhangStyle);

            }
        }
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(3);
        sheet.setColumnWidth(0, 18 * 256);
        sheet.setColumnWidth(1, 32 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 32 * 256);

        int i_index = 0;
        sheet.getRow(i_index).getCell(0).setCellValue("");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getFileName());
        sheet.getRow(i_index).getCell(2).setCellValue(" ");
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getFileName());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("姓名");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getName());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getName());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getName());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("性别");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getSex());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getSex());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getSex());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("出生年月");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getChushengnianyue());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getChushengnianyue());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getChushengnianyue());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("民族");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getMinzu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getMinzu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getMinzu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("籍贯");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getJiguan());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJiguan());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getJiguan());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("出生地");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getChushengdi());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getChushengdi());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getChushengdi());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("政治面貌");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getZhengzhimianmao());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getZhengzhimianmao());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getZhengzhimianmao());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("入党时间");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getRudangshijian());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getRudangshijian());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getRudangshijian());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("参加工作时间");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getCanjiagongzuo());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getCanjiagongzuo());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getCanjiagongzuo());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("来我行时间");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getLaiwohang());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getLaiwohang());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getLaiwohang());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("职务级别");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getZhiwujibie());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getZhiwujibie());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getZhiwujibie());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("专业技术资格");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getJishuzige());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJishuzige());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getJishuzige());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("熟悉专业有何特长");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getShuxizhuanye());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getShuxizhuanye());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getShuxizhuanye());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("专业技术职务");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getJishuzhiwu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJishuzhiwu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getJishuzhiwu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("健康状况");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getJiankang());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJiankang());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getJiankang());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("全日制教育");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getQuanrizhijiaoyu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getQuanrizhijiaoyu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getQuanrizhijiaoyu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("毕业院校及专业");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getRizhixuexiao());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getRizhixuexiao());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getRizhixuexiao());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("在职教育");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getZaizhijiaoyu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getZaizhijiaoyu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getZaizhijiaoyu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("毕业院校及专业");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getZaizhixuexiao());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getZaizhixuexiao());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getZaizhixuexiao());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("现任职务");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getXianrenzhiwu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getXianrenzhiwu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getXianrenzhiwu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("拟任职务");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getNirenzhiwu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getNirenzhiwu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getNirenzhiwu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("拟免职务");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getNimianzhiwu());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getNimianzhiwu());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getNimianzhiwu());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("简历");
        String leftJianliStr =  "";
        if(leftPeople.getJianli() != null){
            leftJianliStr = leftPeople.getJianli();
            leftJianliStr = leftJianliStr.replaceAll(" ", "");
            leftJianliStr = leftJianliStr.trim();
        }
        String rightJianliStr =  "";
        if(rightPeople.getJianli() != null){
            rightJianliStr = rightPeople.getJianli();
            rightJianliStr = rightJianliStr.replaceAll(" ", "");
            rightJianliStr = rightJianliStr.trim();
        }
        sheet.getRow(i_index).getCell(1).setCellValue(leftJianliStr);
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJianli());
        sheet.getRow(i_index).getCell(3).setCellValue(rightJianliStr);
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("奖惩情况");
        String leftJiangchengStr =  "";
        if(leftPeople.getJiangchengqingkuang() != null){
            leftJiangchengStr = leftPeople.getJiangchengqingkuang();
            leftJiangchengStr = leftJiangchengStr.replaceAll(" ", "");
            leftJiangchengStr = leftJiangchengStr.trim();
        }
        String rightJiangchengStr =  "";
        if(rightPeople.getJiangchengqingkuang() != null){
            rightJiangchengStr = rightPeople.getJiangchengqingkuang();
            rightJiangchengStr = rightJiangchengStr.replaceAll(" ", "");
            rightJiangchengStr = rightJiangchengStr.trim();
        }
        sheet.getRow(i_index).getCell(1).setCellValue(leftJiangchengStr);
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJiangchengqingkuang());
        sheet.getRow(i_index).getCell(3).setCellValue(rightJiangchengStr);
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("年度考核结果");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getNiankaohejieguo());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getNiankaohejieguo());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getNiankaohejieguo());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("任免理由");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getRenmianliyou());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getRenmianliyou());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getRenmianliyou());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("呈报单位");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getChengbaodanwei());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getChengbaodanwei());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getChengbaodanwei());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("审批机关意见");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getJiguanyijian());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getJiguanyijian());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getJiguanyijian());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("行政机关任免意见");
        sheet.getRow(i_index).getCell(1).setCellValue(leftPeople.getXingzhengyijian());
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getXingzhengyijian());
        sheet.getRow(i_index).getCell(3).setCellValue(rightPeople.getXingzhengyijian());
        i_index++;

        sheet.getRow(i_index).getCell(0).setCellValue("家庭主要成员");
        sheet.getRow(i_index).getCell(1).setCellValue("");
        sheet.getRow(i_index).getCell(2).setCellValue(resultPeople.getRelationStr());
        sheet.getRow(i_index).getCell(3).setCellValue("");
        i_index++;

        if (leftPeople.getRelationList() != null) {
            for (int i = 0; i < leftPeople.getRelationList().size(); i++) {
                String relationChengwei = leftPeople.getRelationList().get(i).getChengwei();
                String relationName = leftPeople.getRelationList().get(i).getName();
                String relationAge = leftPeople.getRelationList().get(i).getAge();
                String relationZhengzhi = leftPeople.getRelationList().get(i).getZhengzhimianmao();
                String relationGongzuo = leftPeople.getRelationList().get(i).getGongzuodanwei();

                sheet.getRow(i + 31).getCell(1).setCellValue(relationChengwei + "-" + relationName + "-" + relationAge + "-" + relationZhengzhi + "-" + relationGongzuo);
            }
        }

        if (rightPeople.getRelationList() != null) {
            for (int i = 0; i < rightPeople.getRelationList().size(); i++) {
                String relationChengwei = rightPeople.getRelationList().get(i).getChengwei();
                String relationName = rightPeople.getRelationList().get(i).getName();
                String relationAge = rightPeople.getRelationList().get(i).getAge();
                String relationZhengzhi = rightPeople.getRelationList().get(i).getZhengzhimianmao();
                String relationGongzuo = rightPeople.getRelationList().get(i).getGongzuodanwei();

                sheet.getRow(i + 31).getCell(3).setCellValue(relationChengwei + "-" + relationName + "-" + relationAge + "-" + relationZhengzhi + "-" + relationGongzuo);
            }
        }


        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            if ((sheet.getRow(i).getCell(2) + "").equals("false")) {
                if ((sheet.getRow(i).getCell(0) + "").equals("简历") || (sheet.getRow(i).getCell(0) + "").equals("奖惩情况")) {
                    String str_1 = (sheet.getRow(i).getCell(1) + "");
                    String str_2 = (sheet.getRow(i).getCell(3) + "");
                    if (str_1.equals("") || str_2.equals("")) {

                    } else {
                        int cuowuStrIndex = cuowuStrIndex(str_1, str_2);
                        if (cuowuStrIndex == 0) {
                            sheet.getRow(i).getCell(3).setCellStyle(yanseStyle);
                        } else {
                            String cellValue = sheet.getRow(i).getCell(3).getStringCellValue();
//                            String prefix = cellValue.substring(0, cuowuStrIndex);
//                            String suffix = cellValue.substring(cuowuStrIndex);
//                            sheet.getRow(i).getCell(3).setCellValue(prefix + "\"" + suffix + "\""); // 在指定位置插入特殊字符
//                            sheet.getRow(i).getCell(3).setCellStyle(yanseStyle); // 应用样式
//
//                            String cellValue2 = sheet.getRow(i).getCell(1).getStringCellValue();
//                            String prefix2 = cellValue2.substring(0, cuowuStrIndex);
//                            String suffix2 = cellValue2.substring(cuowuStrIndex);
//                            sheet.getRow(i).getCell(1).setCellValue(prefix2 + "\"" + suffix2 + "\"");
                            XSSFRichTextString textStr = new XSSFRichTextString(cellValue);
                            textStr.applyFont(cuowuStrIndex, cellValue.length(), xssfFont);
                            sheet.getRow(i).getCell(3).setCellValue(textStr);
                        }

                    }
                } else {
                    sheet.getRow(i).getCell(3).setCellStyle(yanseStyle);
                }
            }
        }

        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            int strLength = (sheet.getRow(i).getCell(1) + "").length();
            int rightStrLength = (sheet.getRow(i).getCell(3) + "").length();
            if (strLength < rightStrLength) {
                strLength = rightStrLength;
            }
            if (strLength <= 30) {
                continue;
            }
            int beishu = strLength / 30;
            beishu = beishu + 1;
            sheet.getRow(i).setHeightInPoints(26 * beishu);
        }


        String currentPath = Paths.get("").toAbsolutePath().toString();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        currentPath = currentPath + "/" + dateFormat.format(new Date());
        File directory = new File(currentPath);
        if (!directory.exists()) {
            directory.mkdirs();
        }

        String fileName = leftPeople.getFilePeopleName() + "_" + new Date().getTime();
        fileName = currentPath + "/" + fileName + ".xlsx";

        try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
            // 将工作簿写入文件
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static int cuowuStrIndex(String str1, String str2) {
        int str1_length = str1.length();
        for (int i = 0; i < str2.length(); i++) {
            if (i >= str1_length) {
                return i;
            }
            if (str2.charAt(i) != str1.charAt(i)) {
                return i;
            }
        }
        return 0;
    }


    public static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setFillForegroundColor((short) 23);
        style.setFillPattern((short) 1);
        styles.put("title", style);
        return styles;
    }

    public static String splitName(String filePath) {
        String[] strings = filePath.split("\\\\");
        String fileName = strings[strings.length - 1] + "  ";
        fileName = fileName.split("\\.")[0];
        fileName = fileName.replaceAll("\\s", "");
        fileName = fileName.trim();
        String returnName = "";
        if (fileName.indexOf("_") > 0) {
            String[] split = fileName.split("_");
            for (String str : split) {
                if (returnName.equals("")) {
                    returnName = str;
                    continue;
                }
                if (str.length() < returnName.length()) {
                    returnName = str;
                }
            }
        } else if (fileName.indexOf("支行") > 0) {
            returnName = fileName.substring(fileName.indexOf("支行") + 2, fileName.length());
        } else if (fileName.indexOf("分行") > 0) {
            returnName = fileName.substring(fileName.indexOf("分行") + 2, fileName.length());
        } else {
            returnName = fileName;
        }

        return returnName;
    }


}

